
---------- Thêm loại vé---------------
create proc sp_ThemLoaiVe
@TenLoaiVe nchar(10),
@Gia decimal(18,0),
@isdelete bit
 as
 begin transaction
	begin try
	-- Mã tự tăng	
	declare @ma_next nvarchar(10) -- max 7 ki tu
	declare @max int
	select @max = count(MaLoaiVe) + 1 -- MACV ke tiep dang 'CVxxxxx'
	from dbo.LoaiVe
	where MaLoaiVe like 'LV%'
	-- tao maloaive ke tiep
	if(@max >0 and @max <10)
		set @ma_next = 'LV'+ right( '00' + cast(@max as nvarchar(10)) , 5)
	if(@max >=10 and @max <100)
		set @ma_next = 'LV'+ right( '0' + cast(@max as nvarchar(10)) , 5)
	if(@max >=100 and @max <1000)
		set @ma_next = 'LV'+ right( '' + cast(@max as nvarchar(10)) , 5)
	-- kiem ta ton tai
	while(exists(select MaLoaiVe from dbo.LoaiVe where MaLoaiVe = @ma_next))
	begin
	set @max = @max + 1
	set @ma_next = 'LV'+ right( '0' + cast(@max as nvarchar(5)) , 5)
	end
		
		insert into LoaiVe values(@ma_next,@TenLoaiVe,@Gia,@isdelete)
	end try
	begin catch
		raiserror(N'Lỗi CSDL',16,1)
		print ERROR_MESSAGE()
		rollback transaction
	end catch
 commit
go 


 --Danh Sách Loại vé
 create proc sp_DanhSachLoaiVe
 as
 begin transaction
	begin try
		select * from LoaiVe where iDelete='true'
	end try
	begin catch
		raiserror(N'Lỗi CSDL',16,1)
		print ERROR_MESSAGE()
		rollback transaction
	end catch
 commit
 go

--Xóa loại vé 
 create procedure sp_XoaLoaiVe
@MaLoaiVe nvarchar(5)
as
begin transaction
	begin try
		update LoaiVe set isdelete='false' where MaLoaiVe=@MaLoaiVe
	end try	
	begin catch
		raiserror(N'Lỗi CSDL',16,1)
		print ERROR_MESSAGE()
		rollback transaction
	end catch
commit
go

-- Sửa loại vé
create procedure sp_SuaLoaiVe
@MaLoaiVe nvarchar(5),
@TenLoaiVe nchar(10),
@Gia decimal(18,0)
as
begin transaction
	begin try
		update LoaiVe set TenLoaiVe=@TenLoaiVe,Gia=@Gia,isdelete='true' where MaLoaiVe=@MaLoaiVe
	end try	
	begin catch
		raiserror(N'Lỗi CSDL',16,1)
		print ERROR_MESSAGE()
		rollback transaction
	end catch
commit
go

-- Lấy danh sách loại vé theo mã
create proc sp_DanhSachLoaiVeTheoMa
@MaLoaiVe nvarchar(5)
as
	Begin tran
		begin try
			select * from LoaiVe where MaLoaiVe = @MaLoaiVe
		end try
		begin catch
			raiserror(N'Lỗi CSDL',16,1)
			print Error_message()
			rollback tran
		end catch
	commit
go

---------------------------LOẠI MÓN ĂN-----------------------------------

-- Thêm loại món ăn.
create proc sp_ThemLoaiMonAn
@TenLoaiMonAn nvarchar(50),
@isdelete bit
 as
 begin transaction
	begin try
	-- Mã tự tăng	
	declare @ma_next nvarchar(10) -- max 7 ki tu
	declare @max int
	select @max = count(MaLoaiMonAn) + 1 -- MACV ke tiep dang 'CVxxxxx'
	from dbo.LoaiMonAn
	where MaLoaiMonAn like 'LM%'
	-- tao maloaive ke tiep
	if(@max >0 and @max <10)
		set @ma_next = 'LM'+ right( '00' + cast(@max as nvarchar(10)) , 5)
	if(@max >=10 and @max <100)
		set @ma_next = 'LM'+ right( '0' + cast(@max as nvarchar(10)) , 5)
	if(@max >=100 and @max <1000)
		set @ma_next = 'LM'+ right( '' + cast(@max as nvarchar(10)) , 5)
	-- kiem ta ton tai
	while(exists(select MaLoaiMonAn  from dbo.LoaiMonAn where MaLoaiMonAn = @ma_next))
	begin
	set @max = @max + 1
	set @ma_next = 'LM'+ right( '0' + cast(@max as nvarchar(5)) , 5)
	end
		
		insert into LoaiMonAn values(@ma_next,@TenLoaiMonAn,@isdelete)
	end try
	begin catch
		raiserror(N'Lỗi CSDL',16,1)
		print ERROR_MESSAGE()
		rollback transaction
	end catch
 commit
go 

 --Danh Sách Loại Món ăn
 create proc sp_DanhSachLoaiMonAn
 as
 begin transaction
	begin try
		select * from LoaiMonAn where isdelete='true'
	end try
	begin catch
		raiserror(N'Lỗi CSDL',16,1)
		print ERROR_MESSAGE()
		rollback transaction
	end catch
 commit
 go
 
 --Xóa loại món ăn
 create procedure sp_XoaLoaiMonAn
@MaLoaiMonAn nvarchar(6)
as
begin transaction
	begin try
		update LoaiMonAn set isdelete='false' where MaLoaiMonAn=@MaLoaiMonAn
	end try	
	begin catch
		raiserror(N'Lỗi CSDL',16,1)
		print ERROR_MESSAGE()
		rollback transaction
	end catch
commit
go

-- Sửa loại món ăn
create procedure sp_SuaLoaiMonAn
@MaLoaiMon nvarchar(6),
@TenLoaiMon nvarchar(50)
as
begin transaction
	begin try
		update LoaiMonAn set TenLoaiMonAn=@TenLoaiMon,isdelete='true' where MaLoaiMonAn=@MaLoaiMon
	end try	
	begin catch
		raiserror(N'Lỗi CSDL',16,1)
		print ERROR_MESSAGE()
		rollback transaction
	end catch
commit
go

-- Lấy danh sách loại món theo mã
create  proc sp_DanhSachLoaiMonTheoMa
@MaLoaiMon nvarchar(6)
as
	Begin tran
		begin try
			select * from LoaiMonAn where MaLoaiMonAn=@MaLoaiMon
		end try
		begin catch
			raiserror(N'Lỗi CSDL',16,1)
			print Error_message()
			rollback tran
		end catch
	commit
go
-------------------------------------MÓN ĂN------------------------------

create proc sp_MaMonAn_Tang
as
declare @ma_next nvarchar(10) -- max 7 ki tu
	declare @max int
	select @max = count(MaMonAn) + 1 -- MACV ke tiep dang 'CVxxxxx'
	from dbo.MonAn
	where MaMonAn like 'MA%'
	-- tao makh ke tiep
	if(@max >0 and @max <10)
		set @ma_next = 'MA'+ right( '00' + cast(@max as nvarchar(10)) , 5)
	if(@max >=10 and @max <100)
		set @ma_next = 'MA'+ right( '0' + cast(@max as nvarchar(10)) , 5)
	if(@max >=100 and @max <1000)
		set @ma_next = 'MA'+ right( '' + cast(@max as nvarchar(10)) , 5)
	-- kiem ta ton tai
	while(exists(select MaMonAn from dbo.MonAn where MaMonAn = @ma_next))
	begin
	set @max = @max + 1
	set @ma_next = 'MA'+ right( '0' + cast(@max as nvarchar(5)) , 5)
	end
	select @ma_next as Ma
go


-- Thêm món ăn

create proc sp_ThemMonAn 
@MaMonAn nvarchar(10),
@MaLoaiMonAn nvarchar(5),
@TenMonAn nvarchar(50),
@Gia decimal(18,0)
as
	insert into MonAn 
	values(@MaMonAn,@MaLoaiMonAn,@TenMonAn,1,@Gia) 
go 



-- Thêm loại món ăn_NVL.
create 
--alter
proc sp_ThemMonAn_NVL
@MaNVL nvarchar(10),
@MaMonAn nvarchar(5),
@SoLuong int
 as
 begin transaction
	begin try
		if exists(select * from MonAn_NVL where MaNVL = @MaNVL and MaMonAn = @MaMonAn)
		begin
			declare @SoLuongCu int = (select SoLuong from MonAn_NVL where MaNVL = @MaNVL and MaMonAn = @MaMonAn)
			update MonAn_NVL set SoLuong = @SoLuongCu + @SoLuong where MaNVL = @MaNVL and MaMonAn = @MaMonAn
			update NguyenVatLieu set SoLuongTon = SoLuongTon - (@SoLuongCu + @SoLuong) where MaNVL = @MaNVL
		end
		else	
		begin
			insert into MonAn_NVL values(@MaNVL,@MaMonAn,@SoLuong)
			update NguyenVatLieu set SoLuongTon = SoLuongTon-@SoLuong where MaNVL = @MaNVL
		end		
	end try
	begin catch
		raiserror(N'Lỗi CSDL',16,1)
		print ERROR_MESSAGE()
		rollback transaction
	end catch
 commit
go

-- Xóa món ăn_NVL (xóa nguyên vật liệu ra khỏi món)
create proc sp_XoaMonAn_NVL
@MaNVL nvarchar(10)
 as
 begin transaction
	begin try
		delete MonAn_NVL where MaNVL = @MaNVL
	end try
	begin catch
		raiserror(N'Lỗi CSDL',16,1)
		print ERROR_MESSAGE()
		rollback transaction
	end catch
 commit
go 

-- Lên danh sach món ăn


--xoa mon an
create proc [dbo].[sp_XoaMonAn]
@MaMonAn nvarchar(5)
as
begin transaction
	begin try
		delete MonAn_NVL where MaMonAn = @MaMonAn
		delete MonAn where MaMonAn = @MaMonAn
	end try
	begin catch
		raiserror(N'Lỗi CSDL',16,1)
		print ERROR_MESSAGE()
		rollback transaction
	end catch
 commit
go
-- Danh sách nguyên vật liệu món ăn theo mã
create
--alter
proc sp_ChiTiet_NVL
@MaMonAn nvarchar(5)
 as
 begin transaction
	begin try
		select m.MaMonAn, nvl.MaNVL,TenNVL,SoLuong,m.TenMonAn,l.TenLoaiMonAn,m.Gia from LoaiMonAn l, NguyenVatLieu nvl, MonAn_NVL mnvl, MonAn m where m.MaMonAn=mnvl.MaMonAn and mnvl.MaNVL=nvl.MaNVL and l.MaLoaiMonAn = m.MaLoaiMonAn and m.MaMonAn = @MaMonAn
	end try
	begin catch
		raiserror(N'Lỗi CSDL',16,1)
		print ERROR_MESSAGE()
		rollback transaction
	end catch
 commit
go 

--- Danh sách món ăn
create proc sp_DanhSachMonAn
 as
 begin transaction
	begin try
		select m.MaMonAn,m.TenMonAn,l.TenLoaiMonAn,Gia  from MonAn m, LoaiMonAn l where l.MaLoaiMonAn=m.MaLoaiMonAn
	end try
	begin catch
		raiserror(N'Lỗi CSDL',16,1)
		print ERROR_MESSAGE()
		rollback transaction
	end catch
 commit
go 


-- Sửa món ăn
create proc sp_SuaMonAn
@MaMonAn nvarchar(5),
@MaLoaiMonAn nvarchar(6),
@TenMonAn nvarchar(50),
@Gia decimal(18, 0)
 as
 begin transaction
	begin try
		update MonAn set MaLoaiMonAn=@MaLoaiMonAn, TenMonAn=@TenMonAn,Gia=@Gia where MaMonAn=@MaMonAn
	end try
	begin catch
		raiserror(N'Lỗi CSDL',16,1)
		print ERROR_MESSAGE()
		rollback transaction
	end catch
 commit
go 







